package com.oa.core.utils;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 导出数据  每个Sheet 50000条
     *
     * @param sheetName
     * @param rowName
     * @param dataList
     * @param fileName
     * @param response
     * @param <T>
     * @throws Exception
     */
    public static <T> void downloadExcelPage(String sheetName, String[] rowName, List<T> dataList, String fileName,
                                             HttpServletResponse response) throws Exception {
        //工作表名后面的数字，如表1，表2
        int i = 0;
        //记录总行数
        int rownum = 0;
        //记录每个sheet的行数
        int tempnum = 0;
        //分页条数达到此条数则创建工作表
        int page = 50000;
        //创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建列标题栏样式
        HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10);
        HSSFCellStyle style = getStyle(workbook);
        while (true) {
            //创建工作表并应用上面的样式
            HSSFSheet sheet = createWorkbook(sheetName, rowName, workbook, cellStyle, ++i);
            rownum++;
            //将数据库中的数据列表写入excel中
            if (dataList != null) {
                tempnum = 0;
                for (int j = rownum - i; j < dataList.size(); j++) {
                    //进入第2个sheet写出的行数从第2条数据开始(++tempnum等于1，因为标题行占了1行)
                    HSSFRow row2 = sheet.createRow(++tempnum);
                    rownum++;
                    T obj = dataList.get(j);//遍历每个对象
                    Field[] fields = obj.getClass().getDeclaredFields();
                    for (int k = 0; k < rowName.length; k++) {

                        Field field = fields[k];
                        field.setAccessible(true);
                        String type = field.getGenericType().toString();
                        String name = field.getName();
                        // 将属性的首字母大写
                        name = name.replaceFirst(name.substring(0, 1), name.substring(0, 1)
                                .toUpperCase());
                        //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                        HSSFCell cell = row2.createCell(k);
                        //设置单元格的值
                        if (type.equals("class java.util.Date")) {
                            Method m = obj.getClass().getMethod("get" + name);
                            Date value = (Date) m.invoke(obj);
                            cell.setCellValue(value == null ? "" : DateUtil.formatDate(value, "yyyy-MM-dd"));
                        } else {
                            Method m = obj.getClass().getMethod("get" + name);
                            cell.setCellValue(m.invoke(obj) == null ? "" : String.valueOf(m.invoke(obj)));
                        }

                        cell.setCellStyle(style);                                   //设置单元格样式

                    }
                    //达到5条退出并创建另外一个sheet
                    if (rownum % page == 0) {
                        break;
                    }
                }
            }
            //如果记录的行数等于集合的总行数则退出
            if ((rownum - i) == dataList.size()) {
                break;
            }
        }
        if (workbook != null) {
            try {
                if (response != null) {
                    response.setContentType("application/vnd.ms-excel;charset=utf-8");
                    response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints(fontSize);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 创建新工作表
     */
    public static HSSFSheet createWorkbook(String sheetName, String[] rowName, HSSFWorkbook workbook, HSSFCellStyle
            cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet(sheetName + "_" + num);
        sheet.setDefaultColumnWidth(18);

        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < rowName.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(rowName[i]);
        }
        return sheet;
    }

    /*
     * 导出数据
     */
    public static <T> void downloadExcel(String sheetName, String[] rowName, List<T> dataList, String fileName,
                                         HttpServletResponse response) throws Exception {
        HSSFWorkbook workbook = null;               // 创建工作簿对象
        try {
            workbook = new HSSFWorkbook();         // 创建工作表
            HSSFSheet sheet = workbook.createSheet(sheetName);
            sheet.setDefaultColumnWidth(18);
            HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10);
            HSSFCellStyle style = getStyle(workbook);                       //单元格样式对象

            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(0);                // 在索引0的位置创建行

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                cellRowName.setCellValue(rowName[n]);                                 //设置列头单元格的值
                cellRowName.setCellStyle(cellStyle);                       //设置列头单元格样式
            }
            if (dataList != null && !dataList.isEmpty() && dataList.size() > 0) {
                //将查询出的数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {
                    HSSFRow row = sheet.createRow(i + 1);//创建所需的行数
                    T obj = dataList.get(i);//遍历每个对象
                    Field[] fields = obj.getClass().getDeclaredFields();
                    for (int j = 0; j < columnNum; j++) {

                        Field field = fields[j];
                        field.setAccessible(true);
                        String type = field.getGenericType().toString();
//                    System.out.println(field.getName() + ":" + field.get(obj)+"----"+type);

                        //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                        HSSFCell cell = row.createCell(j);
                        //设置单元格的值
                        if ("class java.util.Date".equals(type)) {
                            cell.setCellValue(field.get(obj) == null ? "" : DateUtil.formatDate((Date) field.get(obj), "yyyy-MM-dd"));
                        } else {
                            cell.setCellValue(field.get(obj) == null ? "" : String.valueOf(field.get(obj)));
                        }
                        cell.setCellStyle(style);                                   //设置单元格样式
                    }
                }
            }

            if (workbook != null) {
                try {
                    if (response != null) {
                        //  response.setContentType("application/vnd.ms-excel;charset=utf-8");
                        response.setContentType("application/json;charset=UTF-8");
                        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
                        OutputStream out = response.getOutputStream();
                        workbook.write(out);
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /*
     * 列数据信息单元格样式
     */
    public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
//        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        return style;
    }


    /**
     * 读取excel返回list集合
     *
     * @param t        对象类型
     * @param fileName 文件名
     * @param <T>
     * @return 对象集合
     */
    public static <T> List<T> read(Class<T> t, String fileName, InputStream is) throws Exception {
        StringBuffer errMessage = new StringBuffer();
        List<T> list = new ArrayList<>();
        //创建输入流
        try {
            //是07版本以前的Excel文件缀名为.xls
            if (isExcel2003(fileName)) {
                HSSFWorkbook workbook = new HSSFWorkbook(is);
                //表个数
                int sheetNum = workbook.getNumberOfSheets();
                for (int k = 0; k < sheetNum; k++) {
                    //获取工作表
                    HSSFSheet sheet = workbook.getSheetAt(k);

                    //得到Excel的行数
                    int totalRows = sheet.getLastRowNum() + 1;
                    //获取行,行号作为参数传递给getRow方法,第一行从1开始计算
                    for (int i = 1; i < totalRows; i++) {

                        T newT = t.newInstance();
                        // 获取实体类的所有属性，返回Field数组
                        Field[] fields = newT.getClass().getDeclaredFields();
                        HSSFRow row = sheet.getRow(i);
                        if (row == null) {
                            continue;
                        }
                        int totalCells = row.getLastCellNum();

//                        for (int j = 0; j < totalCells; j++) {
                        for (int j = 0; j < fields.length; j++) {
                            //获取单元格,row已经确定了行号,列号作为参数传递给getCell,第一列从0开始计算
                            HSSFCell cell = row.getCell(j);
                            if (null != cell) {
                                //设置单元格的值
                                Object cellValue = getValue(cell);
                                setValue(fields[j], cellValue == null ? null : cellValue.toString(), newT);
                            }
                        }
                        list.add(newT);
                    }
                }

            } else if (isExcel2007(fileName)) { //07版本以后的Excel文件后缀名为.xlsx
                XSSFWorkbook workbook = new XSSFWorkbook(is);
                //工资表个数
                int sheetNum = workbook.getNumberOfSheets();
                for (int k = 0; k < sheetNum; k++) {
                    //获取工作表
                    XSSFSheet sheet = workbook.getSheetAt(k);
                    //得到Excel的行数
                    int totalRows = sheet.getPhysicalNumberOfRows();
                    //获取行,行号作为参数传递给getRow方法,第一行从1开始计算
                    for (int i = 1; i < totalRows; i++) {
                        T newT = t.newInstance();
                        // 获取实体类的所有属性，返回Field数组
                        Field[] fields = newT.getClass().getDeclaredFields();
                        XSSFRow row = sheet.getRow(i);
                        if (row == null) {
                            continue;
                        }
                        int totalCells = row.getPhysicalNumberOfCells();
                        for (int j = 0; j < totalCells; j++) {
                            //获取单元格,row已经确定了行号,列号作为参数传递给getCell,第一列从0开始计算
                            XSSFCell cell = row.getCell(j);
                            //设置单元格的值
                            if (null != cell) {
                                Object cellValue = getValue(cell);
                                setValue(fields[j], cellValue == null ? null : cellValue.toString(), newT);
                            }
                        }
                        list.add(newT);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (errMessage.toString().equals("")) {
                errMessage.append("数据导入失败");
            }
            throw new Exception(errMessage.toString());
        } finally {
            is.close();
        }
        return list;
    }

    /**
     * T不同属性类型，从excel读取的String类型做转换
     *
     * @param field
     * @param cellValue
     * @param newT
     * @param <T>
     * @throws IllegalAccessException
     */
    public static <T> void setValue(Field field, String cellValue, T newT) throws IllegalAccessException {
        // 要设置属性可达，不然会抛出IllegalAccessException异常
        field.setAccessible(true);
        String type = field.getGenericType().toString();
        if (type.equals("class java.lang.String")) {
            field.set(newT, cellValue);
        }
        if (type.equals("class java.lang.Integer")) {
            field.set(newT, (int) Double.parseDouble(cellValue));
        }
        if (type.equals("class java.lang.Short")) {
            field.set(newT, Short.parseShort(cellValue));
        }
        if (type.equals("class java.lang.Double")) {
            field.set(newT, Double.parseDouble(cellValue));
        }
        if (type.equals("class java.lang.Boolean")) {
            field.set(newT, Boolean.parseBoolean(cellValue));
        }
        if (type.equals("class java.math.BigDecimal")) {
            field.set(newT, new BigDecimal(cellValue));
        }
        if (type.equals("class java.util.Date")) {
            field.set(newT, DateUtil.parseDate(cellValue, "yyyy-MM-dd"));
        }
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            return false;
        }
        return true;
    }

    public static Object getValue(Cell cell) {
        Object object = null;

        switch (cell.getCellType()) {   //根据cell中的类型来输出数据
            case HSSFCell.CELL_TYPE_STRING: //字符串型
                cell.getCellStyle().getDataFormat();
                object = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC: //数值型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = cell.getDateCellValue();
                    object = sdf.format(date);
                } else {
                    object = cell.getNumericCellValue();
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:  //布尔型
                object = cell.getBooleanCellValue();
                break;
            default:
                logger.info("unsuported sell type=======" + cell.getCellType());
                break;
        }
        return object;
    }


}
